This is the accompanying notebook for all data used in the ROOT ACCESS column on bulatlat.com You can modify this to analyze other part of the NEP 2015. This notebook is focused on the ICT component.
Author: Rick Bahague / rick (at) opensourceshoppe (dot) com.
Download datasets from the DBM website.
In [665]:
%matplotlib inline
#%pylab inline
import pandas as pd
import matplotlib.pyplot as plt
import mpld3
import matplotlib.pylab as pylab
import numpy as np
from pylab import *
from IPython.display import Image
pylab.rcParams['figure.figsize'] = 12, 6
pd.set_option('display.max_rows',500)
mpld3.enable_notebook()
DATA_PATH = "../data/"
#durl = 'http://datasets.flowingdata.com/crimeRatesByState2005.csv'
#rdata = genfromtxt(durl,dtype='S8,f,f,f,f,f,f,f,i',delimiter=',')
In [632]:
#Data taken from DBM Site
auto_approp = pd.read_csv(DATA_PATH + "2015 NEP-Proposed-Automatic.csv")
auto_approp.fillna(value=0,inplace=True)
new_approp = pd.read_csv(DATA_PATH + "2015 NEP-Proposed.csv")
new_approp.fillna(value=0,inplace=True)
#rename columns for clarity
new_approp.columns = [u'uacs_dpt_id', u'Department', u'uacs_agy_id', u'Agency',
u'uacs_fpap_id', u'Description', u'operdiv', u'operunit', u'uacs_oper_dsc', u'fndsrc',
u'uacs_fundsubcat_dsc', u'uacs_exp_cd', u'Category', u'uacs_sobj_cd',
u'Objective', u'Amount']
auto_approp.columns = [u'uacs_dpt_id', u'Department', u'uacs_agy_id', u'Agency',
u'uacs_fpap_id', u'Description', u'operdiv', u'operunit', u'uacs_oper_dsc', u'fndsrc',
u'uacs_fundsubcat_dsc', u'uacs_exp_cd', u'Category', u'uacs_sobj_cd',
u'Objective', u'Amount']
#select data for analysis
auto_approp_data = auto_approp[auto_approp.Amount>0][['Department','Agency','Description','Category','Objective','Amount']]
auto_approp_data['NEP'] = 'AUTO'
new_approp_data = new_approp[new_approp.Amount>0][['Department','Agency','Description','Category','Objective','Amount']]
new_approp_data['NEP'] = 'NEW'
all_data = auto_approp_data.append(new_approp_data)
all_data[ [u'NEP',u'Department', u'Agency', u'Description', u'Category', u'Objective', u'Amount']].to_csv('data.csv')
#all_data.head()
In [633]:
all_data['Amount'].sum()
Out[633]:
Sa kabuuan, mas malaki ng Php 341.7B ang gagastusin ng Administrasyon sa susunod na taon kung ihahambing sa budget ngayong taon.
Data from downloaded CSV file do not match data presented in Congress. In the Congress hearing, DBM reported a lesser amount for Capital outlays. Actual Data shows the discrepancy is more than Php 2B. Actual data shows that P2B is taken from Financial Expense and MOOE and added to Capital Outlay.
In [634]:
expense_class = all_data[['Category','Amount']].groupby(['Category']).sum()
expense_class.sort(columns='Amount',ascending=False,inplace=True)
#expense_class
In [635]:
dbm_presentation_expense = [374582000,937593000,532080000,761745000]
dbm_presentation_class = pd.DataFrame(dbm_presentation_expense)
dbm_presentation_class.index=['Financial Expenses','Maintenance and Other Operating Expenses',
'Capital Outlays','Personnel Services']
dbm_presentation_class.columns=['Amount']
dbm_presentation_class.sort_index()
check=dbm_presentation_class.join(expense_class,lsuffix='_DBM',how='left')
#check
In [636]:
#expense_class.sub(dbm_presentation_class)
In [637]:
check.sum()
Out[637]:
In [638]:
departments = all_data[['Department','Amount']].groupby(['Department']).sum()
departments.sort(axis=0,columns='Amount',inplace=True,ascending=False)
t = departments['Amount'].sum()
departments['% Share'] = departments['Amount']/t* 100
departments
Out[638]:
Mahigit 53.8% ng badyet para sa 2015 ay nakalaan sa Internal Revenue Allotment (IRA), Debt Interest Payments, Department of Education (DepEd), at Department of Public Works and Highways (DPWH). Aabot naman sa P389 B ang pondong inilaan para sa mga Local Government Units (LGUs) sa pamamagitan ng IRA, P373 B naman ang awtomatikong inilaan sa pambayad utang ng bansa, P340B ang gagamitin upang suportahan ang mga programa sa edukasyon at P 300B ang inilaan para sa mga proyekto ng DPWH.
In [669]:
Image(url='https://public.tableausoftware.com/views/ICTSpendingontheNationalExpenditureProgram2015/ProposedBudget2015')
Out[669]:
In [639]:
objectives = all_data[['Objective','Amount']].groupby(['Objective']).sum()
objectives.sort(axis=0,columns='Amount',inplace=True,ascending=False)
t = objectives['Amount'].sum()
objectives['Share %'] = objectives['Amount']/t*100
objectives
Out[639]:
The following lists budget items that can be categorized as ICT spending. Note that there are budget items that are actually almost the same but are treated separately and given separate budgets. Example: ICT Software and Computer Software, Information and Communication Technology Equipment and ICT Machinery and Equipment
In [640]:
ICT_items = [
"Information and Communication Technology Equipment",
"Communication Equipment",
"ICT Office Supplies",
"ICT Software",
"Cloud Computing Service",
"ICT Training Expenses",
"Rents - ICT Machinery and Equipment",
"Internet Subscription Expenses",
"ICT Consultancy Services",
"Communication Networks",
"Other General Services - ICT Services",
"ICT Software Subscription",
"Cable, Satellite, Telegraph and Radio Expenses",
"ICT Machinery and Equipment",
"Computer Software",
"Website Maintenance",
"ICT Generation, Transmission and Distribution Expenses",
"ICT Research, Exploration and Development Expenses"]
In [641]:
search = 'Mobile|ICT|Software|Website|Database|Landline|Internet|Cloud|Information and Communication|Communication Equipment|Communication Network|Cable|MITHI|Internet|Computer'
ict_budget_items=all_data[all_data.Objective.str.contains(search,na=0)]
ict_item_department = ict_budget_items.groupby(['Department']).sum()
#ict_item_objectives = ict_budget_items.groupby(['Category','Objective']).sum()
ict_item_objectives = ict_budget_items.groupby(['Objective']).sum()
ict_item_objectives.sort(columns='Amount',ascending=False,inplace=True)
ict_item_department.sort(columns='Amount',ascending=False,inplace=True)
ict_item_objectives['Share %'] = ict_item_objectives['Amount']/ict_item_objectives['Amount'].sum()*100
ict_item_department['Share %'] = ict_item_department['Amount']/ict_item_department['Amount'].sum()*100
#ict_budget_items.to_csv('ICT Budget Items.csv')
#ict_item_department
ict_item_objectives
Out[641]:
We also looked at programmed ICT projects in the NEP. This differ from above. Project implementation costs will include administrative costs and not only ICT services costs.
In [642]:
search_data = 'BalinkBayan Portal|Digitization|Software System|Information System|Data banking|Data Management|Knowledge Management|Information Management|Computerization|Information technology'
ict_project_details = all_data[all_data.Description.str.contains(search_data,na=0)]
a=ict_project_details[ict_project_details.NEP=='NEW'][['Department','Description','Amount']].groupby(['Department','Description']).sum()
ict_projects = a.groupby(level=0).agg(['sum','count'])
#ict_project_details.to_csv('ICT Project Details.csv')
ict_descriptions = a.groupby(level=1).agg(['sum','count'])
ict_descriptions.sort([('Amount','sum')],ascending=False,inplace=True)
ict_descriptions
Out[642]:
In [643]:
ict_project_details.head()
Out[643]:
In [644]:
ict_budget = ict_projects.join(ict_item_department['Amount'],how='right')
ict_budget.fillna(0,inplace=True)
ict_budget.columns = ['Amount of New Project','Count of New Projects','Amount ICT Budget Items']
ict_budget.sort(columns='Amount ICT Budget Items',ascending=False,inplace=True)
#test_spending = ict_budget.join(departments,how='right')
#test_spending.fillna(0)
#percentage = test_spending['Amount ICT Budget Items']/test_spending['Amount']*100
#percentage.fillna(0).sort(ascending=False,inplace=True)
#percentage.sort(axis=0,ascending=False)
#percentage
ict_budget
Out[644]:
The total ICT spending for NEP amounts to the following (in '000' PhP):
In [645]:
ict_budget.sum()
Out[645]:
In [646]:
inc = ['Other Executive Offices','Department of Science and Technology (DOST)']
department = 'Other Executive Offices'
ict_project_details[ict_project_details.Department==department].groupby(['Agency','Description']).sum()
Out[646]:
In [647]:
descr = 'Information System Development and Maintenance'
ict_project_details[ict_project_details.Description == descr]
Out[647]:
In [648]:
descr = 'Information System Development and Maintenance'
descr = 'Information System'
d=ict_project_details[ict_project_details.Description.str.contains(descr)]
d.groupby(['Agency','Objective']).sum()
#e=d.groupby(['Objective']).sum()
#d.groupby(['Agency']).sum()
#e.sort(columns='Amount',ascending=False)
d['Amount'].sum()
Out[648]:
In [649]:
ppp = [
"Internet Subscription Expenses",
"Landline","Mobile","Cable, Satellite, Telegraph and Radio Expenses"]
c=all_data[all_data.Objective.isin(ppp)]
#c['Amount'].sum()/ict_budget['Amount ICT Budget Items'].sum()*100
c['Amount'].sum()
Out[649]:
In [650]:
proprietary = [
'ICT Software Subscription',
'ICT Software',
'Computer Software',"Cloud Computing Service",]
proprietary_expenses = all_data[(all_data.Objective.isin(proprietary))]
In [651]:
proprietary_expenses['Amount'].sum()/ict_budget['Amount ICT Budget Items'].sum()*100
proprietary_expenses['Amount'].sum()
Out[651]:
In [652]:
benefits = [
'Basic Salary - Civilian',
'Retirement and Life Insurance Premiums','Bonus - Civilian',
'PERA - Civilian',
'Cash Gift - Civilian', 'Clothing/Uniform Allowance - Civilian',
'PhilHealth - Civilian',
'Productivity Incentive Allowance - Civilian',
'ECIP - Civilian','Pag-IBIG - Civilian',
'Honoraria - Civilian',
'Salaries and Wages - Casual/Contractual']
personnel_benefits = ict_project_details[ict_project_details.Objective.isin(benefits)]
outsourced = [
'Other General Services - ICT Services',
'Consultancy Services',
'ICT Consultancy Services',
'Cloud Computing Service','Website Maintenance']
outsourced_expenses = ict_project_details[ict_project_details.Objective.isin(outsourced)]
In [653]:
#personnel_benefits['Amount'].sum()/ict_budget['Amount of New Project'].sum()
personnel_benefits['Amount'].sum()
Out[653]:
In [654]:
#outsourced_expenses['Amount'].sum()/ict_budget['Amount of New Project'].sum()
outsourced_expenses['Amount'].sum()
Out[654]:
In [655]:
Web_Maintenance = all_data[all_data.Objective=='Website Maintenance']
temp=Web_Maintenance[['Department','Agency','Amount']].groupby(['Department','Agency']).sum()
temp.sort(columns='Amount',ascending=False,inplace=True)
temp
Out[655]:
In [656]:
#Description, Agency, Department, Category
query_str = 'Rents - ICT Machinery and Equipment'
temp = all_data[all_data.Objective==query_str].groupby(['Department','Agency','Description']).sum()
temp.sort(columns='Amount',ascending=False,inplace=True)
temp
Out[656]:
In [657]:
ict_budget_items['Amount'].sum()/all_data['Amount'].sum()*100
ict_budget_items['Amount'].sum()
Out[657]:
In [658]:
(25165407-10493789)/10493789.
Out[658]:
In [659]:
25165407000/11546000000000.
Out[659]:
In [660]:
300519120-219341196
Out[660]:
In [661]:
389860429-341544726
Out[661]:
In [662]:
2606000000-2264628503
Out[662]:
In [663]:
242850463-180311094
Out[663]:
In [664]:
#Data taken from DBM Site
auto_approp = pd.read_csv(DATA_PATH + "2015 NEP-Adjusted-Automatic.csv")
auto_approp.fillna(value=0,inplace=True)
new_approp = pd.read_csv(DATA_PATH + "2015 NEP-Adjusted.csv")
new_approp.fillna(value=0,inplace=True)
#rename columns for clarity
new_approp.columns = [u'uacs_dpt_id', u'Department', u'uacs_agy_id', u'Agency',
u'uacs_fpap_id', u'Description', u'operdiv', u'operunit', u'uacs_oper_dsc', u'fndsrc',
u'uacs_fundsubcat_dsc', u'uacs_exp_cd', u'Category', u'uacs_sobj_cd',
u'Objective', u'Amount']
auto_approp.columns = [u'uacs_dpt_id', u'Department', u'uacs_agy_id', u'Agency',
u'uacs_fpap_id', u'Description', u'operdiv', u'operunit', u'uacs_oper_dsc', u'fndsrc',
u'uacs_fundsubcat_dsc', u'uacs_exp_cd', u'Category', u'uacs_sobj_cd',
u'Objective', u'Amount']
#select data for analysis
auto_approp_data = auto_approp[auto_approp.Amount>0][['Department','Agency','Description','Category','Objective','Amount']]
auto_approp_data['NEP'] = 'AUTO'
new_approp_data = new_approp[new_approp.Amount>0][['Department','Agency','Description','Category','Objective','Amount']]
new_approp_data['NEP'] = 'NEW'
all_data = auto_approp_data.append(new_approp_data)
#all_data[ [u'NEP',u'Department', u'Agency', u'Description', u'Category', u'Objective', u'Amount']].to_csv('data.csv')
#all_data.head()